Generated code - Calling a stored procedure, SelfServicing

Preface

LLBLGen Pro supports existing stored procedures by offering the ability to define calls to those existing stored procedures. There are two types of stored procedures: procedures which do not return a resultset, called Action Stored Procedures, and procedures which return one or more resultsets, which are called Retrieval Stored Procedures. This section illustrates how call definitions to these stored procedures in your project are generated in code and how you can use them in your code. Each stored procedure call method has one overloaded method which has an extra ref/ByRef parameter, returnValue, which returns the return value of the stored procedure, if that's supported by the target database. (SqlServer for example, supports this).

Retrieval Stored Procedure Calls

When you add a call definition for a retrieval stored procedure, a static/shared method which will call that stored procedure will be added to a class called RetrievalProcedures. When the stored procedure called returns a single resultset (which is the most common approach with SqlServer, with Oracle this would be a stored procedure/function with a single REF CURSOR output parameter), the return value of the generated method will be a DataTable. When the stored procedure returns more than one resultset, the return value of the generated method will be a DataSet, containing each resultset in a separate DataTable. For example, when we include a call definition into our LLBLGen Pro project to the procedure in Northwind called 'CustOrderDetail', taking one parameter, an OrderID, a static method called CustOrderDetail is created, returning a DataTable (because the procedure returns a single resultset) and accepting a single parameter, orderID, which is of type int/Integer because the parameter itself is of type integer. To utilize this method in your own code, you can call it in the following way. Let's pass in the orderID 10254 as parameter value:


Nothing more is needed. This one line of code will pass 10254 as value to the parameter of the stored procedure CustOrderDetail and will return the result in a DataTable object. When a stored procedure has more than one parameter, all parameters are specified as input parameters for the method calling the stored procedure.

Output parameters are also supported. When a stored procedure has an output parameter, a parameter representing the output parameter in the stored procedure is added to the method heading and is defined as 'ref' (C#) or 'ByRef' (VB.NET). Illustrated below is the call to an imaginary stored procedure which returns a datatable, takes 4 input parameters and returns a value in an output parameter:

Action Stored Procedure Calls

If you have added a call to a procedure to your project which does not return a resultset, the static/shared method is added to the class ActionProcedures. Instead of returning a DataTable or DataSet, a method in this class returns an int/Integer, which represents the return value of the ExecuteNonQuery() method, which is the number of rows affected if the database has row counting enabled (and the stored procedure doesn't switch it off). For the rest, the action stored procedure methods work the same as the retrieval stored procedures mentioned above: input parameters are defined as normal parameters for the method and output parameters are defined as ref/ByRef parameters.

Transaction Support

Both Action and Retrieval Procedures can be called within an active transaction. By using the overload which accepts an ITransaction object, (be it a Transaction or a TransactionComPlus object), you can pass in an existing ITransaction instance which contains a valid ADO.NET connection object which is used when calling the stored procedure. This will make the stored procedure to run inside the transaction controlled by the ITransaction object.

Note:

If you pass in a Transaction object, which controls an ADO.NET transaction, keep in mind that that transaction is already a valid database transaction, so when you rollback a transaction inside your stored procedure, you will rollback the entire transaction with that statement, at least on SqlServer.

Wrap call in IRetrievalQuery object

From v2.0 onward, LLBLGen Pro offers you to get the call to a retrieval stored procedure as an IRetrievalQuery object. An IRetrievalQuery object is the query object generated by a Dynamic Query Engine (DQE) and which is executed by the low level fetch logic of LLBLGen Pro's O/R mapper core. The IRetrievalQuery object allows you to fetch a query as a datareader or to project the results of the stored procedure call onto a data-structure of your choice, for example an entity collection. You retrieve an IRetrievalQuery object which wraps the call to a given stored procedure by calling the following generated method (each retrieval stored procedure has such a method generated):


You can then pass the IRetrievalQuery object to the methods for fetching a datareader or fetch a projection. See for more information about fetching a datareader or fetching a projection: LLBLGen Pro - Fetching DataReaders and projections.